﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Entity;

namespace DAO
{
    public class NhapHangDAO
    {
        public static DataTable LayDanhSachTenSP()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                String cmdText = "SELECT TenSP FROM SAN_PHAM";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static DataTable LayDanhSachTenNCC()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                String cmdText = "SELECT TenNCC FROM NHA_CUNG_CAP";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static String LayMaSP(String tensp)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@TenSP", tensp);

                String cmdText = "SELECT MaSP FROM SAN_PHAM WHERE TenSP = @TenSP";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText, par).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }
            return rs.Rows[0][0].ToString();
        }

        public static String LayMaPhieuNhap()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                String cmdText = "Select MaPhieu from PHIEU_NHAP_HANG " +
	                            "where stuff(MaPhieu,1,6,'') = (Select Max(stuff(MaPhieu,1,6,'')) from PHIEU_NHAP_HANG)";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs.Rows[0][0].ToString();
        }

        public static void NhapHang(PhieuNhapHang ph, ListSanPham list)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SanPham_Nhap spnhap = new SanPham_Nhap();
                SqlParameter[] param = new SqlParameter[3];
                param[0] = new SqlParameter("@tennv", ph.tennv);
                param[1] = new SqlParameter("@tongtien", ph.tongtien);
                param[2] = new SqlParameter("@ngaynhap", ph.ngaynhap);
                SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_PhieuNhapHang", param);

                for (int i = 0; i < list.sp.Count; i++)
                {
                    spnhap = list.sp[i];
                    SqlParameter[] param2 = new SqlParameter[5];
                    param2[0] = new SqlParameter("@maphieunhap", LayMaPhieuNhap());
                    param2[1] = new SqlParameter("@masp", spnhap.masp);
                    param2[2] = new SqlParameter("@soluong", spnhap.soluong);
                    param2[3] = new SqlParameter("@dongia", spnhap.dongia);
                    param2[4] = new SqlParameter("@tenncc", spnhap.tenncc);
                    String cmdText2 = "INSERT INTO CHI_TIET_NHAP_HANG(MaPhieuNhap, MaSP, SoLuong, DonGia, TenNCC) values " + 
                        "(@maphieunhap, @masp, @soluong, @dongia, @tenncc)";
                    SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText2, param2);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }
    }
}
